1. Field of the Invention
The invention relates to database systems generally and more particularly to transferring database objects between database systems.
2. Description of Related Art
A database system provides persistent storage for items of data, organizes the items of data into objects, and manipulates the items of data by means of queries which describe a set of objects. A common kind of database system is a relational database system. Among the objects in a relational database system are tables. Each table has one or more columns and zero or more rows. For example, a two-column table called employees may look like this:
NamePayBob50,000.00Tom60,000.00Jack110,000.00
The objects in the Name column are the names of employees; the objects in the Pay column are values specifying the employee's annual salary. A query on the table employees might look like this:                select Name from employees where Pay>100,000;        
This query will return the set of values from the Name column that belong to rows which satisfy the condition that the value of Pay in the row be greater than 100,000. Here, the set of values consists of the name from the third row, namely “Jack”. The query is written in the SQL (Structured Query Language) language. SQL is used to manipulate objects in relational database systems. SQL has two sublanguages: DML (Data Manipulation Language) which is used to manipulate non-metadata objects, and DDL (Data Definition Language), which is used to manipulate metadata objects. For example, the above query is written in the DML sublanguage; DDL was used to define the table employees.
Each object in the database system has an object type that describes how the object is defined and what can be done with it. For example, all table objects in the database system are objects of type table. In terms of how the objects are used in a relational database system, there are two kinds of objects: user objects like the one above that contain information belonging to users of the database system and system objects that contain information belonging to the database system itself. The database system uses the information in the system objects to manage the database system. Among the system objects are tables that contain information which describes all of the other objects in the database system. The information that describes an object is termed the object's metadata and an object that contains metadata for an object is termed herein a metadataobject. An object that does not contain metadata is termed herein an ordinaryobject For example, one of the metadata objects is a table that has an entry for each of the tables, both ordinary and metadata, in the relational database system. Metadata in this table and in other metadata objects associated with it specify the name of the table employees, the names of its columns, the type of data that may be contained in each column, the number of rows currently in the table, and the location in the database system of the actual data for the table employees.
Transferring database objects between database systems is made difficult by the fact that the objects may be very large and by the fact that any such transfer must maintain the objects' organization, including how the objects are ordered and the relationships between them. For example, even a simple table like employees must be transferred in a way which maintains the relationships between employee names and pay. Most database systems have export and import utilities for performing such transfers. The export utility puts the objects into a form which permits another database system to reconstruct the objects and the import utility reconstructs the objects from the exported form. Utilities also exist for importing data into a database from a non-database source.
Import and Export Between Databases
FIG. 1 provides an overview of the export and import utilities that were used previously to the present invention in the relational database systems manufactured by Oracle Corporation, Redwood Shores, Calif. 94065. Complete descriptions of these utilities for the Oracle® 9i database system may be found in Oracle 9i Database Utilities, Release 2, Part Number A96652-01, obtainable from Oracle Corporation. What the export utility does is shown at 101. The utility makes a database 103 containing a number of objects into a serial export file 105. The utility orders the objects in export file 105 such that objects that contain information required to build other objects precede those objects. For example, a table object in a relational database management system may have an index object associated with it. The index object permits quick location of the objects in the table indexed by the index object. In the following, an object which requires information from another object to be built is said to be dependent on the other object. Included in export file 105 but not shown is a copy of the metadata for database 103; it has been converted into SQL Data Definition Language (DDL) statements that can be used in a destination database system with the objects in file 105 to create a copy of database 103 in the destination system.
Once the export file has been made, it is transferred to the destination database management system which is to import the database on the file. Transfer may be by sending the export file across a network or by making a copy of the export file on a portable medium and transferring the portable medium to the database management system which is to import the database on the file. Once the export file is available to the importing database management system, that system's import utility 107 serially reads the export file and uses the DDL to create the objects in file 106 in the destination database management system. Because the export file is ordered such that objects that have dependent objects precede the dependent objects in the export file, the information that export needs to create an object in database 103 is always available at the time the object is being created.
Import from Non-Database Sources
When data is imported into a database system from a non-database source such as a data file, the import operation must describe how the data in the source relates to the objects in the database. For example, if a data file to be loaded into the table employees has the data in the form Bob; 50,000; Tom; 60,000.00; Jack; 110,000; the import operation must specify that the first item in the file corresponds to the first field in a row of employees and the second to the second field in a row.
In the Oracle 9i database system, the utility for importing data into the database system from a non-database source is the SQL*Loader, which is described in the Oracle9i Database Utilities reference cited above. FIG. 2 is an overview of the operation of the SQL*Loader. SQL*Loader 201 takes one or more input data files 203, extracts data items from the files, and writes them into tables in database 103. A log file 207 records the course of the load operation, bad files 209 contain data items that should have been written to database 103 but could not be, and discard files 211 contain data items that were filtered from the items to be written to database 103. Loader control file 205 specifies how the desired data items are to be located in the input data files, what fields in the database tables the data items are to be written to, and how the data items are to be filtered. One way in which loader control file 205 specifies how the desired data items are to be located in the data file is by creating an external table definition in the database system. The data for the external table specified in the external table definition does not exist in the database system. Instead, the definition describes how fields in the external table relate to data items in the external source. The load operation is then specified by means of an update operation which updates fields in the database system's tables from fields in the external table. An important advantage of the external table definition is that it permits the use of facilities in the database system for operating on database objects in parallel in importing data from the external source. If the external table definition defines a table which, if it were a table in the database system, could be operated on in parallel, the load can be done in parallel.
Problems with Existing Export and Import Utilities
Most commercial database systems include utilities like the export, import, and SQL*Loader utilities employed in the Oracle9i database system. While these utilities do perform their intended functions, they have a number of drawbacks which make them inefficient and hard to use:                The utilities for exporting objects to and importing objects from other database systems write and read export files 105 serially; consequently, these utilities cannot take advantage of the database system's ability to perform operations in parallel to speed the export and import operations.        None of the export or import utilities is restartable; if an export or import job is interrupted at any point for any reason, it must be started over again from the beginning.        The only status information provided for an ongoing export or import operation is the log file produced by the utility and many events in the operation do not result in an entry in the log file.        Once an export or import job is started, the operator has no further control over it.        While the utilities may be used to do fileless transfers, none of them has been specifically designed for that purpose.        Current export and import utilities have limited capabilities for transforming metadata during an import operation or filtering and selecting objects during either import or export.It is an object of the present invention to provide export and import utilities that overcome these and other drawbacks of existing export and import utilities.        